package com.carple.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.carple.dto.LoveBoard;
import com.carple.dto.LoveBoardComment;



public class LoveBoardDao {
	
	public void insertBoard(LoveBoard board) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			conn = ConnectionHelper.getConnection();
			conn.setAutoCommit(false);
			StringBuilder sql = new StringBuilder();
			sql.append("INSERT INTO loveboard (title, header, emailid, content) VALUES (?,?,?,?)");
			pstmt = conn.prepareStatement(sql.toString());
			pstmt.setString(1, board.getTitle());
			pstmt.setString(2, board.getHeader());
			pstmt.setString(3, board.getEmailid());
			pstmt.setString(4, board.getContent());
			
			pstmt.executeUpdate();
			
			
//			////groupno에 boardno를 넣기 위한 작업
//			sql.delete(0, sql.length());
//			//LAST_INSERT_ID(): 현재 세션에서 마지막으로 처리된 자동 증가값
//			sql.append("UPDATE board SET groupno = LAST_INSERT_ID() WHERE boardno = LAST_INSERT_ID()");
//			pstmt = conn.prepareStatement(sql.toString());
			
			
			////groupno에 boardno를 넣기 위한 작업 끝
			
		} catch (Exception ex) {
			try{conn.rollback();}catch(Exception ex2){}
			ex.printStackTrace();
		} finally {
			//6. 연결닫기
			if (pstmt != null) try { pstmt.close(); } catch (Exception ex) {}
			if (conn != null) try {conn.setAutoCommit(true);}catch(Exception ex){}
			if (conn != null) try { conn.close(); } catch (Exception ex) {}
		}
	}
	
///////////////////////////////////////////////////////////////

	public List<LoveBoard> getBoardList(int startRow, int count,String header) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;//조회 결과에 접근하는 참조 변수
		//데이터베이스의 데이터를 읽어서 저장할 객체 컬렉션
		ArrayList<LoveBoard> boards = new ArrayList<LoveBoard>();
		
		try {
			conn = ConnectionHelper.getConnection();
			StringBuilder sql = new StringBuilder(512);
			System.out.println("DAO"+header);
			if(header==null){
				sql.append("SELECT boardno, title, emailid, readcount, regdate, deleted, header ")
				.append("FROM loveboard ORDER BY boardno DESC ")
				.append("LIMIT ?,?");
				pstmt = conn.prepareStatement(sql.toString());
				pstmt.setInt(1, startRow);
				pstmt.setInt(2, count);
				System.out.println(pstmt);
				rs = pstmt.executeQuery();
			}else if(header.equals("male")){
				sql.append("SELECT boardno, title, emailid, readcount, regdate, deleted, header ")
				.append("FROM loveboard WHERE header = 'male' ORDER BY boardno DESC ")
				.append("LIMIT ?,?");
				pstmt = conn.prepareStatement(sql.toString());
				pstmt.setInt(1, startRow);
				pstmt.setInt(2, count);
				System.out.println(pstmt);
				rs = pstmt.executeQuery();
			}else if(header.equals("female")){
				sql.append("SELECT boardno, title, emailid, readcount, regdate, deleted, header ")
				.append("FROM loveboard WHERE header = 'female' ")
				.append("LIMIT ?,?");
				pstmt = conn.prepareStatement(sql.toString());
				pstmt.setInt(1, startRow);
				pstmt.setInt(2, count);
				System.out.println(pstmt);
				rs = pstmt.executeQuery();
			}
			
			
			
			while(rs.next()){
				LoveBoard board = new LoveBoard();
				board.setBoardNo(rs.getInt(1));
				board.setTitle(rs.getString(2));
				board.setEmailid(rs.getString(3));
				//board.setContent(rs.getString(4));
				board.setReadCount(rs.getInt(4));
				board.setRegDate(rs.getDate(5));
				board.setDeleted(rs.getBoolean(6));
//				board.setGroupNo(rs.getInt(7));  //필요없는것
//				board.setStep(rs.getInt(8));
//				board.setDepth(rs.getInt(9));
				board.setHeader(rs.getString(7));
				
				
				boards.add(board);
				
			}
			
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			//6. 연결닫기
			if (rs != null) try { rs.close(); } catch (Exception ex) {}
			if (pstmt != null) try { pstmt.close(); } catch (Exception ex) {}
			if (conn != null) try { conn.close(); } catch (Exception ex) {}
		}
		
		return boards;
	}
	
	public LoveBoard viewContent(String str) {
		Connection conn = null;
		PreparedStatement pstmt = null, pstmt2 = null;
		ResultSet rs = null, rs2 = null;
		LoveBoard board = null;//조회 결과에 접근하는 참조 변수
		//데이터베이스의 데이터를 읽어서 저장할 객체 컬렉션
		
		try {
			conn = ConnectionHelper.getConnection();
			StringBuilder sql = new StringBuilder(512);
			sql.append("SELECT boardno, title, emailid, content, readcount, regdate, readcount, header FROM loveboard WHERE boardno = ?");
			pstmt = conn.prepareStatement(sql.toString());
			pstmt.setString(1, str);
			
			rs = pstmt.executeQuery();
			if(rs.next()){
				board = new LoveBoard();
				board.setBoardNo(rs.getInt(1));
				board.setTitle(rs.getString(2));
				board.setEmailid(rs.getString(3));
				board.setContent(rs.getString(4));
				board.setReadCount(rs.getInt(5));
				board.setRegDate(rs.getDate(6));
//				board.setGroupNo(rs.getInt(7));
//				board.setStep(rs.getInt(8));
//				board.setDepth(rs.getInt(9));
				board.setReadCount(rs.getInt(7));
				board.setHeader(rs.getString(8));
				//댓글 조회
				sql.delete(0, sql.length());//StringBuilder 비우기
				sql.append("SELECT commentno, boardno, ")
				   .append("	   emailid, content, regdate ")
				   .append("FROM loveboardcomment ")
				   .append("WHERE boardno = ?");
				pstmt2 = conn.prepareStatement(sql.toString());
				pstmt2.setString(1, str);
				rs2 = pstmt2.executeQuery();
				ArrayList<LoveBoardComment> comments 
					= new ArrayList<LoveBoardComment>();
				while (rs2.next()) {
					LoveBoardComment comment = new LoveBoardComment();
					comment.setCommentNo(rs2.getInt(1));
					comment.setBoardNo(rs2.getInt(2));
					comment.setEmailId(rs2.getString(3));
					comment.setContent(rs2.getString(4));
					comment.setRegDate(rs2.getDate(5));
					comments.add(comment);
				}
				board.setComments(comments);
				
			}
			
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			//6. 연결닫기
			if (rs != null) try { rs.close(); } catch (Exception ex) {}
			if (pstmt != null) try { pstmt.close(); } catch (Exception ex) {}
			if (conn != null) try { conn.close(); } catch (Exception ex) {}
		}
		
		return board;
	}
	
	
	public int getBoardCount(String header) {//페이징할때 필요함..
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;//조회 결과에 접근하는 참조 변수
		int count = 0;
		
		try {
			conn = ConnectionHelper.getConnection();
			//select count(*) from board where header = 'walker';
			if(header == null){
				String sql = ("SELECT COUNT(*) FROM loveboard");
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
			}else if(header.equals("male")){
				String sql = ("select count(*) from loveboard where header = 'male'");
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
			}else if(header.equals("female")){
				String sql = ("select count(*) from loveboard where header = 'female'");
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
			}

			while(rs.next()){
				count = rs.getInt(1);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			//6. 연결닫기
			if (rs != null) try { rs.close(); } catch (Exception ex) {}
			if (pstmt != null) try { pstmt.close(); } catch (Exception ex) {}
			if (conn != null) try { conn.close(); } catch (Exception ex) {}
		}
		
		return count;
	}

	public void deleteBoard(int boardNo) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			conn = ConnectionHelper.getConnection();
			String sql = "UPDATE loveboard SET deleted = true WHERE boardno = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, boardNo);
			System.out.println(boardNo);
			pstmt.executeUpdate();	
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			//연결닫기
			if (pstmt != null) try { pstmt.close(); } catch (Exception ex) {}
			if (conn != null) try { conn.close(); } catch (Exception ex) {}
		}
		
		
	}

	public int updateBoard(LoveBoard board) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		int count = 0;
		try {
			conn = ConnectionHelper.getConnection();
			String sql = 
				"UPDATE loveboard SET title = ?, content = ? WHERE boardno = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, board.getTitle());
			pstmt.setString(2, board.getContent());
			pstmt.setInt(3, board.getBoardNo());
			count = pstmt.executeUpdate();
		} catch (Exception ex) {
			count = 0;
			ex.printStackTrace();
		} finally {
			try { if (pstmt != null) pstmt.close(); } catch (Exception ex) { }
			try { if (conn != null) conn.close(); } catch (Exception ex) { }
		}
		return count;
		
	}

	
	public void increaseBoardReadCount(int number) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			conn = ConnectionHelper.getConnection();
			String sql = 
				"UPDATE loveboard " +
				"SET readcount = readcount + 1 " + 
				"WHERE boardno = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, number);
			pstmt.executeUpdate();
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try { if (pstmt != null) pstmt.close(); } catch (Exception ex) { }
			try { if (conn != null) conn.close(); } catch (Exception ex) { }
		}
	}
	
public void insertComment(LoveBoardComment comment) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			conn = ConnectionHelper.getConnection();
			
			String sql = 
				"INSERT INTO loveboardcomment " +
				"(boardno, emailid, content) " +
				"VALUES (?, ?, ?)";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, comment.getBoardNo());
			pstmt.setString(2, comment.getEmailId());
			pstmt.setString(3, comment.getContent());

			pstmt.executeUpdate();
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (pstmt != null) try { pstmt.close(); } catch (Exception ex) {}
			if (conn != null) try { conn.close(); } catch (Exception ex) {}
		}
	}
	
	public void updateComment(LoveBoardComment comment) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			conn = ConnectionHelper.getConnection();
			
			String sql = 
				"UPDATE loveboardcomment " +
				"SET content = ?" +
				"WHERE commentno = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, comment.getContent());
			pstmt.setInt(2, comment.getCommentNo());

			pstmt.executeUpdate();
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (pstmt != null) try { pstmt.close(); } catch (Exception ex) {}
			if (conn != null) try { conn.close(); } catch (Exception ex) {}
		}
	}
	
	public void deleteComment(int commentNo) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			conn = ConnectionHelper.getConnection();
			
			String sql = 
				"DELETE FROM loveboardcomment " +				
				"WHERE commentno = ?";
			pstmt = conn.prepareStatement(sql);			
			pstmt.setInt(1, commentNo);

			pstmt.executeUpdate();
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (pstmt != null) try { pstmt.close(); } catch (Exception ex) {}
			if (conn != null) try { conn.close(); } catch (Exception ex) {}
		}
	}



}
